Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 7
Performance Engineering Starts at the Design Stage

The most effective way to achieve an optimally performing system is to start at the design stage. In the design stage, you have the most control over what the application will be at the rollout stage. In the design stage, any optimizations that can be made to table layouts, disk configurations, system CPUs, and memory will have the most effect. Changing these components after the system has already been in production can be costly and time consuming.

Of course, not all performance engineering can be done in the design stage. Nobody can perfectly model the users who will access the system or the variances in load over time. These factors call for corrective action after the system is in test and in production, but having engineered performance into the system from the start will make this task easier.

Design Stage

In the design stage, it is important to look not only at the application and the database but at the sizing of the system as well. This chapter looks at some of the ways you can design performance into your system from the very start. Some of the key areas are the layout of the database itself, the use of indexes and clusters, proper design of the application, and sizing of the hardware—including the network.

Database Layout

You can divide the database layout into two sections. The first issue to consider in laying out the tables is the relationship between the data on the system. This relationship determines how the tables are created and which tablespaces they are part of. The second issue to consider in laying out the tables involves their proper placement on the physical disks. The placement of data files on the disks is critical in properly balancing I/Os to avoid a disk bottleneck.

Many tools are available to help you in the design stage. Oracle offers the Designer/2000 (there are many competing tools on the market today) that can help you define the relationships between tables very effectively.

Oracle Designer/2000 is a sophisticated tool that, in conjunction with Oracle’s Developer/2000 development tools, can take you from the business model to the production database. Designer/2000 starts with business modeling that assists with the design of the physical database. Designer/2000 provides both system and process modeling; it is also a design tool that can assist you with the layout of the database, stored procedures, triggers, and so on.

The resulting data generated by the Designer/2000 tools can be used by Developer/2000 to generate applications. Design tools such as Designer/2000—in conjunction with Developer/2000—can improve productivity and enhance the design and performance of the resulting system. Most production databases are extremely complex with many inter-table links. These tools can take some of the complexity out of the design stage and make the database layout more efficient.

Part II of this book, “Tuning the Server,” details why it is important to properly lay out the data to separate random and sequential I/Os. That part of the book also describes how disk arrays can help with this task. It also looks into separating data files from their associated indexes.

Part III of this book, “Configuring the System,” details how different applications call for different configurations. It looks at different workloads such as OLTP, decision support, and batch processing. That part of the book looks at how each design is different and how each can take advantage of the workload characteristics of the system.

Indexes and Clusters

The design phase is the time to decide how to make the most effective use of indexes and clusters. Because the effectiveness of indexes and clusters is related to both the physical design of the database and the application, the design stage is when you can affect both of these elements. Sometimes, it is necessary to re-analyze the database and application to determine whether indexes and clusters are being used correctly; this is not necessary if they are properly designed.

Indexes and clusters are effective only when the application accesses the database in a manner that allows for the use of these features. By properly designing indexes and clusters based on the data access that is required, you can optimize the use of indexes and clusters. To properly optimize your system, design both the application and database based on your knowledge of how this data is to be accessed.

Chapter 10, “Performance Enhancements,” looks at the most effective use of indexes and clusters and how to determine whether you can benefit from using them.

Application Design

The design of the application can also be a deciding factor in the future performance of the system. Carefully plan the application to ensure that deadlocks are avoided and that there is no contention on some limited resources. For example, if your application has everyone accessing the same tables, you may cause undue contention. If your front-end system has excess processing power, take advantage of it by performing input validation there rather than relying on the RDBMS to do it for you.

Look for where the resources will be most in contention and try to design the application to offload work from these areas. Analyze the SQL calls to check for excessive table scans where they may not be necessary. Try to reduce the amount of data sent over the network by taking advantage of stored procedures and packages. Part IV, “Tuning SQL,” looks at these things in detail.

Hardware Sizing

When you decide what hardware to use for your application, make sure that there is sufficient power to handle the load. Often, the workload is underestimated or increases at an unexpected rate. Be sure that you have an upgrade path. Here are some factors to keep in mind when determining the hardware you will use:

  Can the CPUs be upgraded? Can you put in more or faster CPUs?
  Can the I/O system be upgraded? How easy is it to expand? Can you add more disks and controllers easily?
  How much memory does the system support? (If you are already at the limit at rollout, there is no room for expansion.)
  Can you add more network controllers? (Often, you will want to add a second network for backups or to increase user bandwidth.)

Don’t buy more hardware than you need, but if you need to upgrade, make sure that you will not have to start from scratch. Upgrading components is always much less expensive than investing in a new system.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.